{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>提交答卷时间</th>\n",
       "      <th>所用时间</th>\n",
       "      <th>来源</th>\n",
       "      <th>来源详情</th>\n",
       "      <th>来自IP</th>\n",
       "      <th>您的姓名：</th>\n",
       "      <th>1号商品评分：17130435/0434/0431 英纳格(ENICAR) 金至尊蓝牌自动机械对表/男表/女表 售价：6500/6500/11800 毛利30%（分值越高表示商品在该维度表现越好）—主流顾客需求规模</th>\n",
       "      <th>卖点展示可信度</th>\n",
       "      <th>价格合理性</th>\n",
       "      <th>...</th>\n",
       "      <th>新鲜感独特性.12</th>\n",
       "      <th>针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位老师填写）.10</th>\n",
       "      <th>针对此商品综合评价，给予最终选项.12</th>\n",
       "      <th>14号商品17129546 Glasslock钢化玻璃保鲜盒11件套组售价298元 毛利36%（分值越高表示商品在该维度表现越好）—主流顾客需求规模</th>\n",
       "      <th>卖点展示可信度.13</th>\n",
       "      <th>价格合理性.13</th>\n",
       "      <th>新鲜感独特性.13</th>\n",
       "      <th>针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位老师填写）.11</th>\n",
       "      <th>针对此商品综合评价，给予最终选项.13</th>\n",
       "      <th>总分</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2021/3/3 13:45:00</td>\n",
       "      <td>980秒</td>\n",
       "      <td>微信</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58.247.19.229(上海-上海)</td>\n",
       "      <td>施雪红</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>(空)</td>\n",
       "      <td>(空)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>(空)</td>\n",
       "      <td>(空)</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2021/3/3 14:39:21</td>\n",
       "      <td>217秒</td>\n",
       "      <td>微信</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>陆心雨</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>无</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>无</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2021/3/3 14:43:04</td>\n",
       "      <td>3368秒</td>\n",
       "      <td>微信</td>\n",
       "      <td>NaN</td>\n",
       "      <td>222.70.197.251(上海-上海)</td>\n",
       "      <td>温佳敏</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>胡中伟 陈燕萍 宋洁</td>\n",
       "      <td>有建议通过〖明星代言〗</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>(空)</td>\n",
       "      <td>(空)</td>\n",
       "      <td>224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2021/3/3 15:09:28</td>\n",
       "      <td>8423秒</td>\n",
       "      <td>微信</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>皋蓓菁</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>(空)</td>\n",
       "      <td>有建议通过〖紫玉串珠穿线建议用白线，红线不美观〗</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>(空)</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2021/3/3 15:11:24</td>\n",
       "      <td>4084秒</td>\n",
       "      <td>微信</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58.247.19.229(上海-上海)</td>\n",
       "      <td>施雪红</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>时长视具体编排情况再定</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>(空)</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>2021/3/3 15:11:27</td>\n",
       "      <td>5836秒</td>\n",
       "      <td>微信</td>\n",
       "      <td>NaN</td>\n",
       "      <td>114.87.30.170(上海-上海)</td>\n",
       "      <td>李燕</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>(空)</td>\n",
       "      <td>有建议通过〖紫色项链建议透明的线串，比现在玫红色线会好看〗</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>(空)</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>2021/3/3 15:21:14</td>\n",
       "      <td>265秒</td>\n",
       "      <td>微信</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101.230.248.194(上海-上海)</td>\n",
       "      <td>胡珺</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>(空)</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>(空)</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7 rows × 92 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   序号             提交答卷时间   所用时间  来源  来源详情                    来自IP 您的姓名：  \\\n",
       "0   1  2021/3/3 13:45:00   980秒  微信   NaN    58.247.19.229(上海-上海)   施雪红   \n",
       "1   2  2021/3/3 14:39:21   217秒  微信   NaN  101.230.248.194(上海-上海)   陆心雨   \n",
       "2   3  2021/3/3 14:43:04  3368秒  微信   NaN   222.70.197.251(上海-上海)   温佳敏   \n",
       "3   4  2021/3/3 15:09:28  8423秒  微信   NaN  101.230.248.194(上海-上海)   皋蓓菁   \n",
       "4   5  2021/3/3 15:11:24  4084秒  微信   NaN    58.247.19.229(上海-上海)   施雪红   \n",
       "5   6  2021/3/3 15:11:27  5836秒  微信   NaN    114.87.30.170(上海-上海)    李燕   \n",
       "6   7  2021/3/3 15:21:14   265秒  微信   NaN  101.230.248.194(上海-上海)    胡珺   \n",
       "\n",
       "   1号商品评分：17130435/0434/0431 英纳格(ENICAR) 金至尊蓝牌自动机械对表/男表/女表 售价：6500/6500/11800 毛利30%（分值越高表示商品在该维度表现越好）—主流顾客需求规模  \\\n",
       "0                                                  4                                                             \n",
       "1                                                  5                                                             \n",
       "2                                                  4                                                             \n",
       "3                                                  4                                                             \n",
       "4                                                  4                                                             \n",
       "5                                                  4                                                             \n",
       "6                                                  0                                                             \n",
       "\n",
       "   卖点展示可信度  价格合理性  ...  新鲜感独特性.12  \\\n",
       "0        5      5  ...          0   \n",
       "1        5      5  ...          4   \n",
       "2        4      5  ...          4   \n",
       "3        3      4  ...          5   \n",
       "4        5      5  ...          5   \n",
       "5        4      4  ...          4   \n",
       "6        0      0  ...          0   \n",
       "\n",
       "  针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位老师填写）.10  \\\n",
       "0                                                (空)       \n",
       "1                                                  无       \n",
       "2                                         胡中伟 陈燕萍 宋洁       \n",
       "3                                                (空)       \n",
       "4                                        时长视具体编排情况再定       \n",
       "5                                                (空)       \n",
       "6                                                (空)       \n",
       "\n",
       "             针对此商品综合评价，给予最终选项.12  \\\n",
       "0                            (空)   \n",
       "1                          无条件通过   \n",
       "2                    有建议通过〖明星代言〗   \n",
       "3       有建议通过〖紫玉串珠穿线建议用白线，红线不美观〗   \n",
       "4                          无条件通过   \n",
       "5  有建议通过〖紫色项链建议透明的线串，比现在玫红色线会好看〗   \n",
       "6                          无条件通过   \n",
       "\n",
       "   14号商品17129546 Glasslock钢化玻璃保鲜盒11件套组售价298元 毛利36%（分值越高表示商品在该维度表现越好）—主流顾客需求规模  \\\n",
       "0                                                  0                            \n",
       "1                                                  3                            \n",
       "2                                                  0                            \n",
       "3                                                  4                            \n",
       "4                                                  4                            \n",
       "5                                                  4                            \n",
       "6                                                  0                            \n",
       "\n",
       "   卖点展示可信度.13  价格合理性.13  新鲜感独特性.13  \\\n",
       "0           0         0          0   \n",
       "1           4         3          2   \n",
       "2           0         0          0   \n",
       "3           4         4          3   \n",
       "4           4         4          4   \n",
       "5           4         4          4   \n",
       "6           0         0          0   \n",
       "\n",
       "  针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位老师填写）.11 针对此商品综合评价，给予最终选项.13  \\\n",
       "0                                                (空)                     (空)   \n",
       "1                                                  无                   无条件通过   \n",
       "2                                                (空)                     (空)   \n",
       "3                                                (空)                   无条件通过   \n",
       "4                                                (空)                   无条件通过   \n",
       "5                                                (空)                   无条件通过   \n",
       "6                                                (空)                   无条件通过   \n",
       "\n",
       "    总分  \n",
       "0   18  \n",
       "1  221  \n",
       "2  224  \n",
       "3  225  \n",
       "4  241  \n",
       "5  220  \n",
       "6    0  \n",
       "\n",
       "[7 rows x 92 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 载入原始数据\n",
    "df_raw = pd.read_excel('./测试文件.xlsx',sheet_name='Sheet1')\n",
    "df_raw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>序号</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>提交答卷时间</th>\n",
       "      <td>2021/3/3 13:45:00</td>\n",
       "      <td>2021/3/3 14:39:21</td>\n",
       "      <td>2021/3/3 14:43:04</td>\n",
       "      <td>2021/3/3 15:09:28</td>\n",
       "      <td>2021/3/3 15:11:24</td>\n",
       "      <td>2021/3/3 15:11:27</td>\n",
       "      <td>2021/3/3 15:21:14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>所用时间</th>\n",
       "      <td>980秒</td>\n",
       "      <td>217秒</td>\n",
       "      <td>3368秒</td>\n",
       "      <td>8423秒</td>\n",
       "      <td>4084秒</td>\n",
       "      <td>5836秒</td>\n",
       "      <td>265秒</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>来源</th>\n",
       "      <td>微信</td>\n",
       "      <td>微信</td>\n",
       "      <td>微信</td>\n",
       "      <td>微信</td>\n",
       "      <td>微信</td>\n",
       "      <td>微信</td>\n",
       "      <td>微信</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>来源详情</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>价格合理性.13</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新鲜感独特性.13</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位老师填写）.11</th>\n",
       "      <td>(空)</td>\n",
       "      <td>无</td>\n",
       "      <td>(空)</td>\n",
       "      <td>(空)</td>\n",
       "      <td>(空)</td>\n",
       "      <td>(空)</td>\n",
       "      <td>(空)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>针对此商品综合评价，给予最终选项.13</th>\n",
       "      <td>(空)</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>(空)</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>无条件通过</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>总分</th>\n",
       "      <td>18</td>\n",
       "      <td>221</td>\n",
       "      <td>224</td>\n",
       "      <td>225</td>\n",
       "      <td>241</td>\n",
       "      <td>220</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>92 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                    0  \\\n",
       "序号                                                                  1   \n",
       "提交答卷时间                                              2021/3/3 13:45:00   \n",
       "所用时间                                                             980秒   \n",
       "来源                                                                 微信   \n",
       "来源详情                                                              NaN   \n",
       "...                                                               ...   \n",
       "价格合理性.13                                                            0   \n",
       "新鲜感独特性.13                                                           0   \n",
       "针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位老...                (空)   \n",
       "针对此商品综合评价，给予最终选项.13                                               (空)   \n",
       "总分                                                                 18   \n",
       "\n",
       "                                                                    1  \\\n",
       "序号                                                                  2   \n",
       "提交答卷时间                                              2021/3/3 14:39:21   \n",
       "所用时间                                                             217秒   \n",
       "来源                                                                 微信   \n",
       "来源详情                                                              NaN   \n",
       "...                                                               ...   \n",
       "价格合理性.13                                                            3   \n",
       "新鲜感独特性.13                                                           2   \n",
       "针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位老...                  无   \n",
       "针对此商品综合评价，给予最终选项.13                                             无条件通过   \n",
       "总分                                                                221   \n",
       "\n",
       "                                                                    2  \\\n",
       "序号                                                                  3   \n",
       "提交答卷时间                                              2021/3/3 14:43:04   \n",
       "所用时间                                                            3368秒   \n",
       "来源                                                                 微信   \n",
       "来源详情                                                              NaN   \n",
       "...                                                               ...   \n",
       "价格合理性.13                                                            0   \n",
       "新鲜感独特性.13                                                           0   \n",
       "针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位老...                (空)   \n",
       "针对此商品综合评价，给予最终选项.13                                               (空)   \n",
       "总分                                                                224   \n",
       "\n",
       "                                                                    3  \\\n",
       "序号                                                                  4   \n",
       "提交答卷时间                                              2021/3/3 15:09:28   \n",
       "所用时间                                                            8423秒   \n",
       "来源                                                                 微信   \n",
       "来源详情                                                              NaN   \n",
       "...                                                               ...   \n",
       "价格合理性.13                                                            4   \n",
       "新鲜感独特性.13                                                           3   \n",
       "针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位老...                (空)   \n",
       "针对此商品综合评价，给予最终选项.13                                             无条件通过   \n",
       "总分                                                                225   \n",
       "\n",
       "                                                                    4  \\\n",
       "序号                                                                  5   \n",
       "提交答卷时间                                              2021/3/3 15:11:24   \n",
       "所用时间                                                            4084秒   \n",
       "来源                                                                 微信   \n",
       "来源详情                                                              NaN   \n",
       "...                                                               ...   \n",
       "价格合理性.13                                                            4   \n",
       "新鲜感独特性.13                                                           4   \n",
       "针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位老...                (空)   \n",
       "针对此商品综合评价，给予最终选项.13                                             无条件通过   \n",
       "总分                                                                241   \n",
       "\n",
       "                                                                    5  \\\n",
       "序号                                                                  6   \n",
       "提交答卷时间                                              2021/3/3 15:11:27   \n",
       "所用时间                                                            5836秒   \n",
       "来源                                                                 微信   \n",
       "来源详情                                                              NaN   \n",
       "...                                                               ...   \n",
       "价格合理性.13                                                            4   \n",
       "新鲜感独特性.13                                                           4   \n",
       "针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位老...                (空)   \n",
       "针对此商品综合评价，给予最终选项.13                                             无条件通过   \n",
       "总分                                                                220   \n",
       "\n",
       "                                                                    6  \n",
       "序号                                                                  7  \n",
       "提交答卷时间                                              2021/3/3 15:21:14  \n",
       "所用时间                                                             265秒  \n",
       "来源                                                                 微信  \n",
       "来源详情                                                              NaN  \n",
       "...                                                               ...  \n",
       "价格合理性.13                                                            0  \n",
       "新鲜感独特性.13                                                           0  \n",
       "针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位老...                (空)  \n",
       "针对此商品综合评价，给予最终选项.13                                             无条件通过  \n",
       "总分                                                                  0  \n",
       "\n",
       "[92 rows x 7 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 先转置一下\n",
    "df_raw = df_raw.T\n",
    "df_raw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1号商品评分：17130435/0434/0431 英纳格(ENICAR) 金至尊蓝牌自动机...</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>卖点展示可信度</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>价格合理性</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>新鲜感独特性</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>针对MD提到的播放时间段及导演主持人，您的提议有哪些？（请媒体制作中心及编排营销中心两位老师填写）</td>\n",
       "      <td>单价高，建议晚间时段</td>\n",
       "      <td>无</td>\n",
       "      <td>杨董蓉 王燕华</td>\n",
       "      <td>(空)</td>\n",
       "      <td>单价高，建议晚间上新</td>\n",
       "      <td>(空)</td>\n",
       "      <td>(空)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>价格合理性.13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>新鲜感独特性.13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位...</td>\n",
       "      <td>(空)</td>\n",
       "      <td>无</td>\n",
       "      <td>(空)</td>\n",
       "      <td>(空)</td>\n",
       "      <td>(空)</td>\n",
       "      <td>(空)</td>\n",
       "      <td>(空)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>针对此商品综合评价，给予最终选项.13</td>\n",
       "      <td>(空)</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>(空)</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>无条件通过</td>\n",
       "      <td>无条件通过</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>总分</td>\n",
       "      <td>18</td>\n",
       "      <td>221</td>\n",
       "      <td>224</td>\n",
       "      <td>225</td>\n",
       "      <td>241</td>\n",
       "      <td>220</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>85 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                index           0      1  \\\n",
       "0   1号商品评分：17130435/0434/0431 英纳格(ENICAR) 金至尊蓝牌自动机...           4      5   \n",
       "1                                             卖点展示可信度           5      5   \n",
       "2                                               价格合理性           5      5   \n",
       "3                                              新鲜感独特性           4      3   \n",
       "4   针对MD提到的播放时间段及导演主持人，您的提议有哪些？（请媒体制作中心及编排营销中心两位老师填写）  单价高，建议晚间时段      无   \n",
       "..                                                ...         ...    ...   \n",
       "80                                           价格合理性.13         NaN      3   \n",
       "81                                          新鲜感独特性.13         NaN      2   \n",
       "82  针对MD提出的商品播放时间段及导演主持人人选，您的提议有哪些？（请媒体制作及编排营销中心两位...         (空)      无   \n",
       "83                                针对此商品综合评价，给予最终选项.13         (空)  无条件通过   \n",
       "84                                                 总分          18    221   \n",
       "\n",
       "          2      3           4      5      6  \n",
       "0         4      4           4      4    NaN  \n",
       "1         4      3           5      4    NaN  \n",
       "2         5      4           5      4    NaN  \n",
       "3         4      3           4      3    NaN  \n",
       "4   杨董蓉 王燕华    (空)  单价高，建议晚间上新    (空)    (空)  \n",
       "..      ...    ...         ...    ...    ...  \n",
       "80      NaN      4           4      4    NaN  \n",
       "81      NaN      3           4      4    NaN  \n",
       "82      (空)    (空)         (空)    (空)    (空)  \n",
       "83      (空)  无条件通过       无条件通过  无条件通过  无条件通过  \n",
       "84      224    225         241    220    NaN  \n",
       "\n",
       "[85 rows x 8 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 跳过表头 前 7 行\n",
    "df_raw = df_raw.iloc[7:].reset_index()\n",
    "df_raw = df_raw.replace(0,np.nan)\n",
    "df_raw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>分类</th>\n",
       "      <th>商品号</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1号商品评分：17130435/0434/0431 英纳格(ENICAR) 金至尊蓝牌自动机...</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>卖点展示可信度</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>价格合理性</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>新鲜感独特性</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2号商品 17128932 英纳格(ENICAR)月相大师五月花号真钻纪念男表售价11800...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>卖点展示可信度.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>价格合理性.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>新鲜感独特性.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3号商品17130079 海鸥星耀九州足金陀飞轮腕表售价39800元 毛利40%（分值越高表...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>卖点展示可信度.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>价格合理性.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>新鲜感独特性.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>4号商品17130603 上海牌天文台系列镂空陀飞轮钻石金表售价19999元 毛利36%（分...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>卖点展示可信度.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>价格合理性.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>新鲜感独特性.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>5号商品17130609/0610 美国U.S.POLO ASSN.女/男士长袖衬衫售价29...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>卖点展示可信度.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>价格合理性.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>新鲜感独特性.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>6号商品17130666 奥腾(Haut) 男士英伦格纹时尚休闲茄克售价199元 毛利40%...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>卖点展示可信度.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>价格合理性.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>新鲜感独特性.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>7号商品17130969 上海皇廷花园酒店双人度假套餐售价1199元 毛利11%（分值越高表...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>卖点展示可信度.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>价格合理性.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>新鲜感独特性.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>8号商品17131059 上航国旅周庄铂尔曼酒店双人度假套餐售价999元 毛利11%（分值越...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>卖点展示可信度.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>价格合理性.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>新鲜感独特性.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>9号商品17131001 上航国旅 厦门泉州人文美食4日游【二次提报】售价2799元 毛利1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>卖点展示可信度.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>价格合理性.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>新鲜感独特性.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>10号商品17130782 摩洛哥进口 DermArgan黛摩阿甘阿甘油[【Q】]售价279...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>卖点展示可信度.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>价格合理性.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>新鲜感独特性.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>11号商品17130980 Rosense玫瑰水3+2超值组[【Q】]售价399元 毛利36...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>卖点展示可信度.10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>价格合理性.10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>新鲜感独特性.10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>12号商品17130962/0961 周大福“17916系列”22K金项链/22K金小方戒指...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>卖点展示可信度.11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>价格合理性.11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>新鲜感独特性.11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>13号商品117131174-1175 老洪祥 孤品“翡翠售价5800-198000元 毛利...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>卖点展示可信度.12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>价格合理性.12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>新鲜感独特性.12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>14号商品17129546 Glasslock钢化玻璃保鲜盒11件套组售价298元 毛利36...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>卖点展示可信度.13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>可信度</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>价格合理性.13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>新鲜感独特性.13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                index    0  1    2  3  4  5  \\\n",
       "0   1号商品评分：17130435/0434/0431 英纳格(ENICAR) 金至尊蓝牌自动机...    4  5    4  4  4  4   \n",
       "1                                             卖点展示可信度    5  5    4  3  5  4   \n",
       "2                                               价格合理性    5  5    5  4  5  4   \n",
       "3                                              新鲜感独特性    4  3    4  3  4  3   \n",
       "6   2号商品 17128932 英纳格(ENICAR)月相大师五月花号真钻纪念男表售价11800...  NaN  5    5  5  4  4   \n",
       "7                                           卖点展示可信度.1  NaN  5    5  4  5  4   \n",
       "8                                             价格合理性.1  NaN  4    4  4  5  4   \n",
       "9                                            新鲜感独特性.1  NaN  4    5  5  5  4   \n",
       "12  3号商品17130079 海鸥星耀九州足金陀飞轮腕表售价39800元 毛利40%（分值越高表...  NaN  5    6  4  4  3   \n",
       "13                                          卖点展示可信度.2  NaN  5    6  4  5  4   \n",
       "14                                            价格合理性.2  NaN  5    6  4  5  4   \n",
       "15                                           新鲜感独特性.2  NaN  5    6  4  4  4   \n",
       "18  4号商品17130603 上海牌天文台系列镂空陀飞轮钻石金表售价19999元 毛利36%（分...  NaN  6    5  4  4  4   \n",
       "19                                          卖点展示可信度.3  NaN  6    4  5  4  4   \n",
       "20                                            价格合理性.3  NaN  6    4  4  5  4   \n",
       "21                                           新鲜感独特性.3  NaN  6    5  4  4  4   \n",
       "24  5号商品17130609/0610 美国U.S.POLO ASSN.女/男士长袖衬衫售价29...  NaN  3    5  4  3  4   \n",
       "25                                          卖点展示可信度.4  NaN  4    5  3  4  4   \n",
       "26                                            价格合理性.4  NaN  4    6  5  4  4   \n",
       "27                                           新鲜感独特性.4  NaN  2    4  3  4  4   \n",
       "30  6号商品17130666 奥腾(Haut) 男士英伦格纹时尚休闲茄克售价199元 毛利40%...  NaN  4    4  4  5  4   \n",
       "31                                          卖点展示可信度.5  NaN  3    4  4  5  4   \n",
       "32                                            价格合理性.5  NaN  4    4  4  5  4   \n",
       "33                                           新鲜感独特性.5  NaN  3    4  3  5  3   \n",
       "36  7号商品17130969 上海皇廷花园酒店双人度假套餐售价1199元 毛利11%（分值越高表...  NaN  2    5  4  4  4   \n",
       "37                                          卖点展示可信度.6  NaN  3    5  5  4  4   \n",
       "38                                            价格合理性.6  NaN  2    6  4  4  4   \n",
       "39                                           新鲜感独特性.6  NaN  6    5  5  4  5   \n",
       "42  8号商品17131059 上航国旅周庄铂尔曼酒店双人度假套餐售价999元 毛利11%（分值越...  NaN  5    5  4  4  4   \n",
       "43                                          卖点展示可信度.7  NaN  5    5  5  4  4   \n",
       "44                                            价格合理性.7  NaN  5    5  4  4  4   \n",
       "45                                           新鲜感独特性.7  NaN  5    5  4  4  5   \n",
       "48  9号商品17131001 上航国旅 厦门泉州人文美食4日游【二次提报】售价2799元 毛利1...  NaN  2    4  4  3  4   \n",
       "49                                          卖点展示可信度.8  NaN  4    4  4  4  4   \n",
       "50                                            价格合理性.8  NaN  4    5  4  5  4   \n",
       "51                                           新鲜感独特性.8  NaN  3    5  4  4  4   \n",
       "54  10号商品17130782 摩洛哥进口 DermArgan黛摩阿甘阿甘油[【Q】]售价279...  NaN  5    5  4  5  4   \n",
       "55                                          卖点展示可信度.9  NaN  5    4  4  5  4   \n",
       "56                                            价格合理性.9  NaN  5    4  4  5  4   \n",
       "57                                           新鲜感独特性.9  NaN  5    5  3  5  4   \n",
       "60  11号商品17130980 Rosense玫瑰水3+2超值组[【Q】]售价399元 毛利36...  NaN  3    4  4  4  4   \n",
       "61                                         卖点展示可信度.10  NaN  5    4  4  5  4   \n",
       "62                                           价格合理性.10  NaN  3    4  4  5  4   \n",
       "63                                          新鲜感独特性.10  NaN  3    5  5  4  4   \n",
       "66  12号商品17130962/0961 周大福“17916系列”22K金项链/22K金小方戒指...  NaN  2  NaN  3  3  3   \n",
       "67                                         卖点展示可信度.11  NaN  3  NaN  4  4  4   \n",
       "68                                           价格合理性.11  NaN  2  NaN  4  4  3   \n",
       "69                                          新鲜感独特性.11  NaN  5  NaN  4  3  3   \n",
       "72  13号商品117131174-1175 老洪祥 孤品“翡翠售价5800-198000元 毛利...  NaN  2    4  4  3  4   \n",
       "73                                         卖点展示可信度.12  NaN  2    4  4  5  4   \n",
       "74                                           价格合理性.12  NaN  2    4  4  5  4   \n",
       "75                                          新鲜感独特性.12  NaN  4    4  5  5  4   \n",
       "78  14号商品17129546 Glasslock钢化玻璃保鲜盒11件套组售价298元 毛利36...  NaN  3  NaN  4  4  4   \n",
       "79                                         卖点展示可信度.13  NaN  4  NaN  4  4  4   \n",
       "80                                           价格合理性.13  NaN  3  NaN  4  4  4   \n",
       "81                                          新鲜感独特性.13  NaN  2  NaN  3  4  4   \n",
       "\n",
       "      6     分类  商品号  \n",
       "0   NaN   需求规模    1  \n",
       "1   NaN    可信度    1  \n",
       "2   NaN  价格合理性    1  \n",
       "3   NaN    新鲜感    1  \n",
       "6   NaN   需求规模    2  \n",
       "7   NaN    可信度    2  \n",
       "8   NaN  价格合理性    2  \n",
       "9   NaN    新鲜感    2  \n",
       "12  NaN   需求规模    3  \n",
       "13  NaN    可信度    3  \n",
       "14  NaN  价格合理性    3  \n",
       "15  NaN    新鲜感    3  \n",
       "18  NaN   需求规模    4  \n",
       "19  NaN    可信度    4  \n",
       "20  NaN  价格合理性    4  \n",
       "21  NaN    新鲜感    4  \n",
       "24  NaN   需求规模    5  \n",
       "25  NaN    可信度    5  \n",
       "26  NaN  价格合理性    5  \n",
       "27  NaN    新鲜感    5  \n",
       "30  NaN   需求规模    6  \n",
       "31  NaN    可信度    6  \n",
       "32  NaN  价格合理性    6  \n",
       "33  NaN    新鲜感    6  \n",
       "36  NaN   需求规模    7  \n",
       "37  NaN    可信度    7  \n",
       "38  NaN  价格合理性    7  \n",
       "39  NaN    新鲜感    7  \n",
       "42  NaN   需求规模    8  \n",
       "43  NaN    可信度    8  \n",
       "44  NaN  价格合理性    8  \n",
       "45  NaN    新鲜感    8  \n",
       "48  NaN   需求规模    9  \n",
       "49  NaN    可信度    9  \n",
       "50  NaN  价格合理性    9  \n",
       "51  NaN    新鲜感    9  \n",
       "54  NaN   需求规模   10  \n",
       "55  NaN    可信度   10  \n",
       "56  NaN  价格合理性   10  \n",
       "57  NaN    新鲜感   10  \n",
       "60  NaN   需求规模   11  \n",
       "61  NaN    可信度   11  \n",
       "62  NaN  价格合理性   11  \n",
       "63  NaN    新鲜感   11  \n",
       "66  NaN   需求规模   12  \n",
       "67  NaN    可信度   12  \n",
       "68  NaN  价格合理性   12  \n",
       "69  NaN    新鲜感   12  \n",
       "72  NaN   需求规模   13  \n",
       "73  NaN    可信度   13  \n",
       "74  NaN  价格合理性   13  \n",
       "75  NaN    新鲜感   13  \n",
       "78  NaN   需求规模   14  \n",
       "79  NaN    可信度   14  \n",
       "80  NaN  价格合理性   14  \n",
       "81  NaN    新鲜感   14  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 每 6 行作循环，提取前 4 行，添加列，并进行拼接\n",
    "df = pd.DataFrame()\n",
    "totol_rows = len(df_raw)\n",
    "nums = totol_rows // 6\n",
    "rows_format = 6 * nums\n",
    "\n",
    "new_list = [[i,i + 6] for i in range(0,rows_format,6)]\n",
    "for i_j in new_list:\n",
    "    i,j = i_j\n",
    "    temp_df = df_raw[i:j].iloc[:4]\n",
    "    temp_df['分类'] = ['需求规模','可信度','价格合理性','新鲜感']\n",
    "    df = df.append(temp_df)\n",
    "\n",
    "temp_list = list(i for i in range(1,nums + 1))\n",
    "df['商品号'] = [i for i in temp_list for j in range(4)]\n",
    "df "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>商品号</th>\n",
       "      <th>分类</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>可信度</td>\n",
       "      <td>0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>387</th>\n",
       "      <td>13</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388</th>\n",
       "      <td>14</td>\n",
       "      <td>需求规模</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>389</th>\n",
       "      <td>14</td>\n",
       "      <td>可信度</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390</th>\n",
       "      <td>14</td>\n",
       "      <td>价格合理性</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391</th>\n",
       "      <td>14</td>\n",
       "      <td>新鲜感</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>392 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     商品号     分类 variable  value\n",
       "0      1   需求规模        0    4.0\n",
       "1      1    可信度        0    5.0\n",
       "2      1  价格合理性        0    5.0\n",
       "3      1    新鲜感        0    4.0\n",
       "4      2   需求规模        0    NaN\n",
       "..   ...    ...      ...    ...\n",
       "387   13    新鲜感        6    NaN\n",
       "388   14   需求规模        6    NaN\n",
       "389   14    可信度        6    NaN\n",
       "390   14  价格合理性        6    NaN\n",
       "391   14    新鲜感        6    NaN\n",
       "\n",
       "[392 rows x 4 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 逆透视\n",
    "df = df.drop(columns='index')\n",
    "df_melt = pd.melt(df,id_vars=['商品号','分类'])\n",
    "df_melt['value'] = df_melt['value'].astype(float)\n",
    "df_melt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>分类</th>\n",
       "      <th>价格合理性</th>\n",
       "      <th>可信度</th>\n",
       "      <th>新鲜感</th>\n",
       "      <th>需求规模</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>商品号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.67</td>\n",
       "      <td>4.33</td>\n",
       "      <td>3.50</td>\n",
       "      <td>4.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.20</td>\n",
       "      <td>4.60</td>\n",
       "      <td>4.60</td>\n",
       "      <td>4.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.80</td>\n",
       "      <td>4.80</td>\n",
       "      <td>4.60</td>\n",
       "      <td>4.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.60</td>\n",
       "      <td>4.60</td>\n",
       "      <td>4.60</td>\n",
       "      <td>4.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4.60</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.40</td>\n",
       "      <td>3.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4.20</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.60</td>\n",
       "      <td>4.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4.00</td>\n",
       "      <td>4.20</td>\n",
       "      <td>5.00</td>\n",
       "      <td>3.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4.40</td>\n",
       "      <td>4.60</td>\n",
       "      <td>4.60</td>\n",
       "      <td>4.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4.40</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>4.40</td>\n",
       "      <td>4.40</td>\n",
       "      <td>4.40</td>\n",
       "      <td>4.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>4.00</td>\n",
       "      <td>4.40</td>\n",
       "      <td>4.20</td>\n",
       "      <td>3.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3.25</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.75</td>\n",
       "      <td>2.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>3.80</td>\n",
       "      <td>3.80</td>\n",
       "      <td>4.40</td>\n",
       "      <td>3.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3.75</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.25</td>\n",
       "      <td>3.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     mean                  \n",
       "    value                  \n",
       "分类  价格合理性   可信度   新鲜感  需求规模\n",
       "商品号                        \n",
       "1    4.67  4.33  3.50  4.17\n",
       "2    4.20  4.60  4.60  4.60\n",
       "3    4.80  4.80  4.60  4.40\n",
       "4    4.60  4.60  4.60  4.60\n",
       "5    4.60  4.00  3.40  3.80\n",
       "6    4.20  4.00  3.60  4.20\n",
       "7    4.00  4.20  5.00  3.80\n",
       "8    4.40  4.60  4.60  4.40\n",
       "9    4.40  4.00  4.00  3.40\n",
       "10   4.40  4.40  4.40  4.60\n",
       "11   4.00  4.40  4.20  3.80\n",
       "12   3.25  3.75  3.75  2.75\n",
       "13   3.80  3.80  4.40  3.40\n",
       "14   3.75  4.00  3.25  3.75"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 再透视，保留 2 位小数\n",
    "df_pivot = pd.pivot_table(df_melt,index=['商品号'],columns=['分类'],values=['value'],aggfunc=['mean'],fill_value=0).applymap(lambda x:round(x,2))\n",
    "df_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "生成结果文件成功！！\n"
     ]
    }
   ],
   "source": [
    "# 拍平多层索引，输出结果\n",
    "df_pivot.columns = ['_'.join(col) for col in df_pivot.columns.values]\n",
    "df_pivot.to_excel('./测试结果.xlsx',sheet_name='result')\n",
    "print('生成结果文件成功！！')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
